package com.cpi.cornpp.purchase.program.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.purchase.program.dto.PurchaseOrderDTO;
import com.cpi.cornpp.purchase.program.exception.PurchaseProgramException;
import com.cpi.cornpp.purchaseorder.dto.PurchaseOrderAssignFutureDTO;
import com.cpi.cornpp.transports.exception.TransportException;
import com.cs.common.init.SystemParameterCollection;
import com.cs.common.init.beans.SystemParameterBean;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

public class PurchaseOrderDAO extends DBDAO {
	private static final String SQL_INSERT_PURCHASE_ORDER = "INSERT INTO purchases_orders (biweek_period_id, transport_id, plant_id, code, code_number, capacity, corn_type_id) VALUEs (?, ?, ?, ?, ?, ?, ?)";//$NON-NLS-1$
	private static final String SQL_UPDATE_POSTFIX_PURCHASE_ORDER = "UPDATE purchases_orders SET code_number = code_number+1 WHERE code = ? and code_number >= ?";
	private static final String SQL_UPDATE_PURCHASE_ORDER = "UPDATE purchases_orders SET biweek_period_id = ?, transport_id = ?, plant_id = ?, code = ?, code_number = ?, capacity = ?, corn_type_id = ?";//$NON-NLS-1$
	private static final String SQL_UPDATE_BUSHEL_PRICE = "UPDATE purchases_orders SET bushel_price = ? where purchase_order_id = ?";//$NON-NLS-1$
	private static final String SQL_SELECT_PURCHASE_ORDER = "SELECT po.purchase_order_id, po.biweek_period_id, po.transport_id, po.plant_id, po.code, po.code_number, po.capacity, po.corn_type_id, tra.quantity as realCapacity, po.capacity*( select qty from units where unit_id = ?),bushel_price FROM purchases_orders po left join po_planned_reals por on po.purchase_order_id = por.purchase_order_id left join vw_transit_corn tra on tra.po_number = por.po_number";//$NON-NLS-1$
	private static final String SQL_INSERT_PURCHASE_ASSIGN_FUTURE = "INSERT INTO purchases_orders_futures (purchase_order_id,corn_future_id,quantity) values (?, ?, ?)";//$NON-NLS-1$
	private static final String SQL_DELETE_PURCHASE_ASSIGN_FUTURE = "DELETE FROM purchases_orders_futures WHERE purchase_order_id = ? AND corn_future_id = ?";//$NON-NLS-1$
	private static final String SQL_UPDATE_PURCHASE_ASSING_FUTURE = "UPDATE purchases_orders_futures SET quantity = ? WHERE purchase_order_id = ? AND corn_future_id = ?";//$NON-NLS-1$
	private static final String SQL_SELECT_PURCHASE_ASSING_FUTURE = "SELECT COUNT(*) FROM purchases_orders_futures where purchase_order_id = ? AND corn_future_id = ?";//$NON-NLS-1$
	private static final String SQL_SELECT_PURCHASE_ORDERS_FUTURE = "SELECT * FROM purchases_orders_futures where corn_future_id = ?";//$NON-NLS-1$
	private static final String CONTRACT_UNIT_ID_LABEL = "CONTACT_UNIT_ID";//$NON-NLS-1$
	private static final String SQL_UPDATE_COMMISSION = "update purchases_orders_costs set amount = ? where cost_element_id = (select cost_element_id from costs_elements where short_name = ?) and purchase_order_id = ?";//$NON-NLS-1$
	private static final String SQL_INSERT_COMMISSION = "insert into purchases_orders_costs (purchase_order_id, cost_element_id, amount) select ?, cost_element_id, ? from costs_elements where short_name = ?";//$NON-NLS-1$
	private static final String SQL_SELECT_COMMISSION = "select amount from purchases_orders_costs where cost_element_id = (select cost_element_id from costs_elements where short_name = ?) and purchase_order_id = ?";//$NON-NLS-1$
	private static final String COMMISSION_ID_LABEL = "COMMISSION_ID_LABEL";//$NON-NLS-1$
	
	public int insertPurchaseOrderGenerator(PurchaseOrderDTO purchaseOrderDTO) throws PurchaseProgramException{
		int purchaseOrderId = 0; 
		PreparedStatement pstm = null;
		try{
			if (purchaseOrderDTO != null){
				pstm = prepareSQL(SQL_INSERT_PURCHASE_ORDER, dsName);
				pstm.setObject(1, purchaseOrderDTO.getBiWeekPeriodId());
				pstm.setObject(2, purchaseOrderDTO.getTransportId());
				pstm.setObject(3, purchaseOrderDTO.getPlantId());
				pstm.setString(4, purchaseOrderDTO.getCode());
				pstm.setInt(5, purchaseOrderDTO.getPostFix());
				pstm.setObject(6, purchaseOrderDTO.getCapacity());
				pstm.setObject(7, purchaseOrderDTO.getCornTypeId());
				executeInsert(pstm);	
				purchaseOrderId = Integer.parseInt(getInsertedId(pstm.getConnection()));
			} else {
				PurchaseProgramException ppe = new PurchaseProgramException(MSG_NO_INSERT, PurchaseProgramException.LAYER_DAO, PurchaseProgramException.ACTION_INSERT);
				
				throw ppe;
			}
		} catch (PoolException poe) {
			PurchaseProgramException ppe = new PurchaseProgramException(poe, TransportException.LAYER_DAO, TransportException.ACTION_INSERT);
			poe.printStackTrace(System.out);
			throw ppe;
		} catch (SQLException sqle){
			PurchaseProgramException ppe = new PurchaseProgramException(sqle, TransportException.LAYER_DAO, TransportException.ACTION_INSERT);
			sqle.printStackTrace(System.out);
			throw ppe;
		} finally {
			try{
				closeConnection(pstm.getConnection());
			} catch(SQLException sqle){
			}
		}
		return purchaseOrderId;
	}
	
	public int insertPurchaseOrder(PurchaseOrderDTO purchaseOrderDTO) throws PurchaseProgramException{
		int purchaseOrderId = 0; 
		PreparedStatement pstm = null;
		try{
			if (purchaseOrderDTO != null){
				pstm = prepareSQL(SQL_UPDATE_POSTFIX_PURCHASE_ORDER, dsName);
				pstm.setString(1, purchaseOrderDTO.getCode());
				pstm.setInt(2, purchaseOrderDTO.getPostFix());
				executeUpdate(pstm);
				closeConnection(pstm.getConnection());
				
				pstm = prepareSQL(SQL_INSERT_PURCHASE_ORDER, dsName);
				pstm.setObject(1, purchaseOrderDTO.getBiWeekPeriodId());
				pstm.setObject(2, purchaseOrderDTO.getTransportId());
				pstm.setObject(3, purchaseOrderDTO.getPlantId());
				pstm.setString(4, purchaseOrderDTO.getCode());
				pstm.setInt(5, purchaseOrderDTO.getPostFix());
				pstm.setObject(6, purchaseOrderDTO.getCapacity());
				pstm.setObject(7, purchaseOrderDTO.getCornTypeId());
				executeInsert(pstm);
				purchaseOrderId = Integer.parseInt(getInsertedId(pstm.getConnection()));
			} else {
				PurchaseProgramException ppe = new PurchaseProgramException(MSG_NO_INSERT, PurchaseProgramException.LAYER_DAO, PurchaseProgramException.ACTION_INSERT);
				throw ppe;
			}
		} catch (PoolException poe) {
			PurchaseProgramException ppe = new PurchaseProgramException(poe, TransportException.LAYER_DAO, TransportException.ACTION_INSERT);
			poe.printStackTrace(System.out);
			throw ppe;
		} catch (SQLException sqle){
			PurchaseProgramException ppe = new PurchaseProgramException(sqle, TransportException.LAYER_DAO, TransportException.ACTION_INSERT);
			sqle.printStackTrace(System.out);
			throw ppe;
		} finally {
			try{
				closeConnection(pstm.getConnection());
			} catch(SQLException sqle){
			}
		}
		return purchaseOrderId;
	}
	
	public int updateBushelPurchaseOrder(int purchaseOrderId, double bushelPrice) throws PurchaseProgramException{
		int updatedRows = 0;
		PreparedStatement pstm = null;
		try{
			pstm = prepareSQL(SQL_UPDATE_BUSHEL_PRICE, dsName);
			pstm.setDouble(1, bushelPrice);
			pstm.setInt(2, purchaseOrderId);
			updatedRows = executeUpdate(pstm);
		} catch (PoolException poe) {
			PurchaseProgramException ppe = new PurchaseProgramException(poe, TransportException.LAYER_DAO, TransportException.ACTION_UPDATE);
			poe.printStackTrace(System.out);
			throw ppe;
		} catch (SQLException sqle){
			PurchaseProgramException ppe = new PurchaseProgramException(sqle, TransportException.LAYER_DAO, TransportException.ACTION_UPDATE);
			sqle.printStackTrace(System.out);
			throw ppe;
		} finally {
			try{
				closeConnection(pstm.getConnection());
			} catch(SQLException sqle){
				sqle.printStackTrace(System.out);
			}
		}
		return updatedRows;
	}
	
	public int updatePurchaseOrderById(PurchaseOrderDTO purchaseOrderDTO) throws PurchaseProgramException{
		int updatedRows = 0;
		PreparedStatement pstm = null;
		StringBuffer sql = null;
		
		try{
			
			if (purchaseOrderDTO != null){
				sql = new StringBuffer(SQL_UPDATE_PURCHASE_ORDER);
				
				sql.append(SQL_WHERE);
				sql.append(PurchaseOrderDTO.PURCHASE_ORDER_ID);
				sql.append(PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT);
				
				pstm = prepareSQL(sql.toString(), dsName);
				
				pstm.setObject(1, purchaseOrderDTO.getBiWeekPeriodId());
				pstm.setObject(2, purchaseOrderDTO.getTransportId());
				pstm.setObject(3, purchaseOrderDTO.getPlantId());
				pstm.setObject(4, purchaseOrderDTO.getCode());
				pstm.setObject(5, new Integer (purchaseOrderDTO.getPostFix()));
				pstm.setObject(6, purchaseOrderDTO.getCapacity());
				pstm.setObject(7, purchaseOrderDTO.getCornTypeId());
				pstm.setObject(8, purchaseOrderDTO.getPurchaseOrderId());
				
				updatedRows = executeUpdate(pstm);
			} else {
				PurchaseProgramException ppe = new PurchaseProgramException(MSG_NO_UPDATE, PurchaseProgramException.LAYER_DAO, PurchaseProgramException.ACTION_UPDATE);
				
				throw ppe;
			}
			
		} catch (PoolException poe) {
			PurchaseProgramException ppe = new PurchaseProgramException(poe, TransportException.LAYER_DAO, TransportException.ACTION_UPDATE);
			
			poe.printStackTrace(System.out);
			
			throw ppe;
		} catch (SQLException sqle){
			PurchaseProgramException ppe = new PurchaseProgramException(sqle, TransportException.LAYER_DAO, TransportException.ACTION_UPDATE);
			
			sqle.printStackTrace(System.out);
			
			throw ppe;
		} finally {
			
			try{
				
				closeConnection(pstm.getConnection());
				
			} catch(SQLException sqle){
				sqle.printStackTrace(System.out);
			}
		}
		
		return updatedRows;
	}
	public PurchaseOrderDTO getPurchaseOrderById(PurchaseOrderDTO purchaseOrderDTO) throws PurchaseProgramException{
		PreparedStatement pstm = null;
		ResultSet rs = null;
		StringBuffer sql = null;
		String contractUnitId = null;
		SystemParameterCollection systemParameterCollection = null;
		SystemParameterBean systemParameterBean = null;
		try{
			systemParameterCollection = SystemParameterCollection.getInstance();
			systemParameterBean = systemParameterCollection.getSystemParameterBean(CONTRACT_UNIT_ID_LABEL);
			contractUnitId = systemParameterBean.getValue();
			if (purchaseOrderDTO != null){
				sql = new StringBuffer(SQL_SELECT_PURCHASE_ORDER);
				
				sql.append(SQL_WHERE);
				sql.append("po.purchase_order_id");
				sql.append(PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT);
				
				pstm = prepareSQL(sql.toString(), dsName);
				
				pstm.setString(1, contractUnitId);
				pstm.setObject(2, purchaseOrderDTO.getPurchaseOrderId());
				
				rs = executeQuery(pstm);
				
				while(rs.next()){
					purchaseOrderDTO = new PurchaseOrderDTO();
					purchaseOrderDTO.setPurchaseOrderId(new Integer(rs.getInt(1)));
					purchaseOrderDTO.setBiWeekPeriodId(new Integer(rs.getInt(2)));
					purchaseOrderDTO.setTransportId(new Integer(rs.getInt(3)));
					purchaseOrderDTO.setPlantId(new Integer(rs.getInt(4)));
					purchaseOrderDTO.setCode(rs.getString(5));
					purchaseOrderDTO.setPostFix(rs.getInt(6));
					purchaseOrderDTO.setCapacity(new Double(rs.getDouble(7)));
					purchaseOrderDTO.setCornTypeId(new Integer(rs.getInt(8)));
					purchaseOrderDTO.setRealCapacity(new Double(rs.getDouble(9)));
					purchaseOrderDTO.setCapacityConverted(rs.getDouble(10));
					purchaseOrderDTO.setBushelPrice(rs.getDouble(11));
				}
			}
			
		} catch(PoolException poe){
			PurchaseProgramException ppe = new PurchaseProgramException(poe, TransportException.LAYER_DAO, TransportException.ACTION_SELECT);
			
			poe.printStackTrace(System.out);
			
			throw ppe;
		} catch(SQLException sqle){
			PurchaseProgramException ppe = new PurchaseProgramException(sqle, TransportException.LAYER_DAO, TransportException.ACTION_SELECT);
			
			sqle.printStackTrace(System.out);
			
			throw ppe;
		} finally {
			
			try{
				
				closeConnection(pstm.getConnection());
				
			} catch(SQLException sqle){
				sqle.printStackTrace(System.out);
			}
		}
		
		return purchaseOrderDTO;
	}
	
	public List getPurchaseOrdersByTransportId(Integer transportId) throws PurchaseProgramException{
		PurchaseOrderDTO purchaseOrderDTO = null;
		List purchaseOrders = null;
		
		purchaseOrders = new ArrayList(0);
		
		if (transportId != null){
			purchaseOrderDTO = new PurchaseOrderDTO();
			
			purchaseOrderDTO.setTransportId(transportId);
			
			purchaseOrders = getPurchaseOrdersByField(purchaseOrderDTO);
		}
		
		return purchaseOrders;
	}
	
	public List getPurchaseOrdersByPlantId(Integer plantId) throws PurchaseProgramException{
		PurchaseOrderDTO purchaseOrderDTO = null;
		List purchaseOrders = null;
		
		purchaseOrders = new ArrayList(0);
		
		if (plantId != null){
			purchaseOrderDTO = new PurchaseOrderDTO();
			
			purchaseOrderDTO.setPlantId(plantId);
			
			purchaseOrders = getPurchaseOrdersByField(purchaseOrderDTO);
		}
		
		return purchaseOrders;
	}
	
	private List getPurchaseOrdersByField(PurchaseOrderDTO purchaseOrderDTO) throws PurchaseProgramException{
		Object fieldValue = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List purchaseOrders = null;
		StringBuffer sql = null;
		
		purchaseOrders = new ArrayList(0);
		sql = new StringBuffer(SQL_SELECT_PURCHASE_ORDER);
		int contractUnitId = 0;
		SystemParameterCollection systemParameterCollection = null;
		SystemParameterBean systemParameterBean = null;
		try{
			systemParameterCollection = SystemParameterCollection.getInstance();
			systemParameterBean = systemParameterCollection.getSystemParameterBean(CONTRACT_UNIT_ID_LABEL);
			contractUnitId = systemParameterBean.getIntValue();
			if ((purchaseOrderDTO != null) && ((purchaseOrderDTO.getBiWeekPeriodId() != null) || (purchaseOrderDTO.getCapacity() != null) ||
					(purchaseOrderDTO.getCode() != null) || (purchaseOrderDTO.getPlantId() != null) || (purchaseOrderDTO.getPurchaseOrderId() != null) ||
					(purchaseOrderDTO.getTransportId() != null))){
				sql.append(SQL_WHERE);
				
				if (purchaseOrderDTO.getTransportId() != null){
					fieldValue = purchaseOrderDTO.getTransportId();
					sql.append(PurchaseOrderDTO.PURCHASE_ORDER_TRANSPORT_ID); 
				} else if(purchaseOrderDTO.getPlantId() != null){
					fieldValue = purchaseOrderDTO.getPlantId();
					sql.append(PurchaseOrderDTO.PURCHASE_ORDER_PLANT_ID);
				}
				
				sql.append(PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT);
			}
			
			pstm = prepareSQL(sql.toString(), dsName);
			
			pstm.setInt(1, contractUnitId);
			if (sql.toString().indexOf(PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT) != -1){
				pstm.setObject(2, fieldValue);
			}
			
			rs = executeQuery(pstm);
			
			purchaseOrders = mapRows(rs);
			
		}  catch(PoolException poe){
			PurchaseProgramException ppe = new PurchaseProgramException(poe, TransportException.LAYER_DAO, TransportException.ACTION_SELECT);
			
			poe.printStackTrace(System.out);
			
			throw ppe;
		} catch(SQLException sqle){
			PurchaseProgramException ppe = new PurchaseProgramException(sqle, TransportException.LAYER_DAO, TransportException.ACTION_SELECT);
			
			sqle.printStackTrace(System.out);
			
			throw ppe;
		} finally {
			
			try{
				
				closeConnection(pstm.getConnection());
				
			} catch(SQLException sqle){
				sqle.printStackTrace(System.out);
			}
		}
		
		return purchaseOrders;
	}
	
	private PurchaseOrderDTO mapRow(ResultSet rs, int index) throws SQLException{
		PurchaseOrderDTO purchaseOrderDTO = null;
		
		if ((rs != null) && (rs.absolute(index))){
			purchaseOrderDTO = new PurchaseOrderDTO();
			
			purchaseOrderDTO.setPurchaseOrderId(new Integer(rs.getInt(1)));
			purchaseOrderDTO.setBiWeekPeriodId(new Integer(rs.getInt(2)));
			purchaseOrderDTO.setTransportId(new Integer(rs.getInt(3)));
			purchaseOrderDTO.setPlantId(new Integer(rs.getInt(4)));
			purchaseOrderDTO.setCode(rs.getString(5));
			purchaseOrderDTO.setPostFix(rs.getInt(6));
			purchaseOrderDTO.setCapacity(new Double(rs.getDouble(7)));
			purchaseOrderDTO.setRealCapacity(new Double(rs.getDouble(8)));
			purchaseOrderDTO.setCapacityConverted(rs.getDouble(9));
			purchaseOrderDTO.setBushelPrice(rs.getDouble(10));
			purchaseOrderDTO.setCornTypeId(new Integer(rs.getInt(11)));
			
		}
		
		return purchaseOrderDTO;
	}
	
	private List mapRows(ResultSet rs) throws SQLException{
		List purchaseOrders = null;
		
		purchaseOrders = new ArrayList(0);
		
		if (rs != null){
			
			for (; rs.next(); ){
				purchaseOrders.add(mapRow(rs, rs.getRow()));
			}
		}
		
		return purchaseOrders;
	}
	
	public void insertPuchaseOrderAssignFuture(PurchaseOrderAssignFutureDTO purchaseOrderAssignFutureDTO) throws PurchaseProgramException{
		PreparedStatement pstm = null;
		try{
			if (purchaseOrderAssignFutureDTO != null){
				pstm = prepareSQL(SQL_INSERT_PURCHASE_ASSIGN_FUTURE, dsName);
				pstm.setInt(1, purchaseOrderAssignFutureDTO.getPurchaseOrderId());
				pstm.setInt(2, purchaseOrderAssignFutureDTO.getCornFutureId());
				pstm.setDouble(3, purchaseOrderAssignFutureDTO.getQuantity());
				executeInsert(pstm);
			} else {
				PurchaseProgramException ppe = new PurchaseProgramException(MSG_NO_INSERT, PurchaseProgramException.LAYER_DAO, PurchaseProgramException.ACTION_INSERT);
				throw ppe;
			}	
		} catch (PoolException poe) {
			PurchaseProgramException ppe = new PurchaseProgramException(poe, PurchaseProgramException.LAYER_DAO, PurchaseProgramException.ACTION_INSERT);
			poe.printStackTrace(System.out);
			
			throw ppe;
		} catch (SQLException sqle){
			PurchaseProgramException ppe = new PurchaseProgramException(sqle, PurchaseProgramException.LAYER_DAO, PurchaseProgramException.ACTION_INSERT);	
			sqle.printStackTrace(System.out);
			throw ppe;
		} finally {
			try{
				closeConnection(pstm.getConnection());
			} catch(SQLException sqle){
				sqle.printStackTrace(System.out);
			}
		}
	}
	
	public void deletePurchaseOrderAssignFuture(PurchaseOrderAssignFutureDTO purchaseOrderAssignFutureDTO)throws PurchaseProgramException{
		PreparedStatement pstm = null;
		try{
			pstm = prepareSQL(SQL_DELETE_PURCHASE_ASSIGN_FUTURE, dsName);
			pstm.setInt(1, purchaseOrderAssignFutureDTO.getPurchaseOrderId());
			pstm.setInt(2, purchaseOrderAssignFutureDTO.getCornFutureId());
			executeUpdate(pstm);	
		}catch (PoolException poolException){
			PurchaseProgramException purchaseProgramException = null;
			poolException.printStackTrace(System.out);
			purchaseProgramException = new PurchaseProgramException (poolException, PurchaseProgramException.LAYER_DAO,PurchaseProgramException.ACTION_DELETE);
			throw purchaseProgramException;
		} catch(SQLException sqlException){
			PurchaseProgramException purchaseProgramException = null;
			sqlException.printStackTrace(System.out);
			purchaseProgramException = new PurchaseProgramException (sqlException, PurchaseProgramException.LAYER_DAO,PurchaseProgramException.ACTION_DELETE);
			throw purchaseProgramException;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
	}

	public void updatePurchaseOrderAssignFuture(PurchaseOrderAssignFutureDTO purchaseOrderAssignFutureDTO)throws PurchaseProgramException {
		PreparedStatement pstm = null;
		try{
			pstm = prepareSQL(SQL_UPDATE_PURCHASE_ASSING_FUTURE, dsName);
			pstm.setDouble(1, purchaseOrderAssignFutureDTO.getQuantity());
			pstm.setInt(2, purchaseOrderAssignFutureDTO.getPurchaseOrderId());
			pstm.setInt(3, purchaseOrderAssignFutureDTO.getCornFutureId());
			executeUpdate(pstm);
		}catch (PoolException poolException){
			PurchaseProgramException purchaseProgramException = null;
			poolException.printStackTrace(System.out);
			purchaseProgramException = new PurchaseProgramException (poolException, PurchaseProgramException.LAYER_DAO,PurchaseProgramException.ACTION_UPDATE);
			throw purchaseProgramException;
		} catch(SQLException sqlException){
			PurchaseProgramException purchaseProgramException = null;
			sqlException.printStackTrace(System.out);
			purchaseProgramException = new PurchaseProgramException (sqlException, PurchaseProgramException.LAYER_DAO,PurchaseProgramException.ACTION_UPDATE);
			throw purchaseProgramException;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
	}
	
	public boolean existPurchaseOrderAssignFuture(PurchaseOrderAssignFutureDTO purchaseOrderAssignFutureDTO) throws PurchaseProgramException{
		boolean exist = false;
		PreparedStatement pstm = null;
		ResultSet result = null;
		try{
			pstm = prepareSQL(SQL_SELECT_PURCHASE_ASSING_FUTURE, dsName);
			pstm.setInt(1, purchaseOrderAssignFutureDTO.getPurchaseOrderId());
			pstm.setInt(2, purchaseOrderAssignFutureDTO.getCornFutureId());
			result = executeQuery(pstm);
			if (result.next()) {
				exist = result.getBoolean(1);
			}
			
		}catch (PoolException poolException){
			PurchaseProgramException purchaseProgramException = null;
			poolException.printStackTrace(System.out);
			purchaseProgramException = new PurchaseProgramException (poolException, PurchaseProgramException.LAYER_DAO,PurchaseProgramException.ACTION_UPDATE);
			throw purchaseProgramException;
		} catch(SQLException sqlException){
			PurchaseProgramException purchaseProgramException = null;
			sqlException.printStackTrace(System.out);
			purchaseProgramException = new PurchaseProgramException (sqlException, PurchaseProgramException.LAYER_DAO,PurchaseProgramException.ACTION_UPDATE);
			throw purchaseProgramException;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		return exist;
	}
	
	public boolean existPurchaseOrderFutureByCornFutureId(int cornFutureId) throws PurchaseProgramException{
		boolean exist = false;
		PreparedStatement pstm = null;
		ResultSet result = null;
		try{
			pstm = prepareSQL(SQL_SELECT_PURCHASE_ORDERS_FUTURE, dsName);
			pstm.setInt(1, cornFutureId);			
			result = executeQuery(pstm);
			if (result.next()) {
				exist = result.getBoolean(1);
			}
			
		}catch (PoolException poolException){
			PurchaseProgramException purchaseProgramException = null;
			poolException.printStackTrace(System.out);
			purchaseProgramException = new PurchaseProgramException (poolException, PurchaseProgramException.LAYER_DAO,PurchaseProgramException.ACTION_UPDATE);
			throw purchaseProgramException;
		} catch(SQLException sqlException){
			PurchaseProgramException purchaseProgramException = null;
			sqlException.printStackTrace(System.out);
			purchaseProgramException = new PurchaseProgramException (sqlException, PurchaseProgramException.LAYER_DAO,PurchaseProgramException.ACTION_UPDATE);
			throw purchaseProgramException;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		return exist;
	}
	
	public void updateCostCommission(int purchaseOrderId, double commission)throws PurchaseProgramException{
		PreparedStatement preparedStatement =  null;		
		String commissionId = null;
		SystemParameterCollection systemParameterCollection = null;
		SystemParameterBean systemParameterBean = null;
		int updatedRecords = 0;
		try {
			systemParameterCollection = SystemParameterCollection.getInstance();
			systemParameterBean = systemParameterCollection.getSystemParameterBean(COMMISSION_ID_LABEL);
			commissionId = systemParameterBean.getValue();
			preparedStatement = prepareSQL(SQL_UPDATE_COMMISSION, dsName);
			preparedStatement.setDouble(1, commission);
			preparedStatement.setString(2, commissionId);
			preparedStatement.setInt(3, purchaseOrderId);
			updatedRecords = executeUpdate(preparedStatement);
			if( updatedRecords == 0 ){
				preparedStatement = prepareSQL(SQL_INSERT_COMMISSION, dsName);
				preparedStatement.setInt(1, purchaseOrderId);
				preparedStatement.setDouble(2, commission);
				preparedStatement.setString(3, commissionId);
				executeUpdate(preparedStatement);
			}
		}catch (PoolException poolException){
			PurchaseProgramException purchaseProgramException = null;
			purchaseProgramException = new PurchaseProgramException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
			poolException.printStackTrace(System.out);
			throw purchaseProgramException;
		}catch (SQLException sqlException){
			PurchaseProgramException purchaseProgramException = null;
			purchaseProgramException = new PurchaseProgramException(sqlException, CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
			sqlException.printStackTrace(System.out);
			throw purchaseProgramException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
	}
	
	public double getCostCommission(int purchaseOrderId)throws PurchaseProgramException{
		double commission = 0;
		PreparedStatement pstm = null;
		ResultSet result = null;
		String commissionId = null;
		SystemParameterCollection systemParameterCollection = null;
		SystemParameterBean systemParameterBean = null;
		try{
			systemParameterCollection = SystemParameterCollection.getInstance();
			systemParameterBean = systemParameterCollection.getSystemParameterBean(COMMISSION_ID_LABEL);
			commissionId = systemParameterBean.getValue();
			pstm = prepareSQL(SQL_SELECT_COMMISSION, dsName);
			pstm.setString(1, commissionId);
			pstm.setInt(2, purchaseOrderId);
			result = executeQuery(pstm);
			if (result.next()) {
				commission = result.getDouble(1);
			}
		}catch (PoolException poolException){
			PurchaseProgramException purchaseProgramException = null;
			poolException.printStackTrace(System.out);
			purchaseProgramException = new PurchaseProgramException (poolException, PurchaseProgramException.LAYER_DAO,PurchaseProgramException.ACTION_SELECT);
			throw purchaseProgramException;
		} catch(SQLException sqlException){
			PurchaseProgramException purchaseProgramException = null;
			sqlException.printStackTrace(System.out);
			purchaseProgramException = new PurchaseProgramException (sqlException, PurchaseProgramException.LAYER_DAO,PurchaseProgramException.ACTION_SELECT);
			throw purchaseProgramException;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		return commission;
	}
}